﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LCL.Caching;
using LCL.LData;
using System.Data;
using System.Diagnostics;

namespace LCL.DbObjects.Oracle
{
    [Serializable]
    [DebuggerDisplay("{ConnectionString}")]
    public class DbObject : DbModelBase, IDbObject
    {
        public List<DataBaseModel> GetDBList(string host)
        {
            if (string.IsNullOrWhiteSpace(host)) return new List<DataBaseModel>();
            string cacheKey = string.Format(CacheKey_DBLIST, host);
            return cache.Get(cacheKey, () =>
            {
                List<DataBaseModel> dbmList = new List<DataBaseModel>();
                var dt = DbHelper.ExecuteDataTable("select * from v$database order by name ", null);
                foreach (DataRow row in dt.Rows)
                {
                    string dbName = row["Name"].ToString();
                    DataBaseModel dbm = new DataBaseModel();
                    dbm.DatabaseName = dbName;
                    dbmList.Add(dbm);
                }
                return dbmList;
            });
        }

        public List<TableModel> GetTables(string dbName, bool iscol = false)
        {
            if (string.IsNullOrWhiteSpace(dbName)) return new List<TableModel>();
            string cacheKey = string.Format(CacheKey_TABLELIST, dbName);
            return cache.Get(cacheKey, () =>
            {
                var list = new List<TableModel>();
                var dt = DbHelper.ExecuteDataTable("select * from all_tab_comments where table_type = 'TABLE' and owner = '" + DbConfig.DbUser.ToUpper() + "' and instr(table_name,'$')=0 and table_name not like '%BAK%' order by table_name ", null);
                int count = dt.Rows.Count;
                foreach (DataRow row in dt.Rows)
                {
                    string tableName = row["TABLE_NAME"].ToString();
                    if (!this.ArrNoTableslist.Contains(tableName))
                    {
                        string comments = row["COMMENTS"].ToString();
                        var tm = new TableModel();
                        tm.TableName = tableName;
                        tm.TableNameRemark = comments;
                        list.Add(tm);
                    }
                }
                return list;
            });
        }

        public TableModel GetTableInfo(string dbName, string tableName)
        {
            if (string.IsNullOrWhiteSpace(dbName) || string.IsNullOrWhiteSpace(tableName)) return null;
            string cacheKey = string.Format(CacheKey_TABLE_INFO, dbName, tableName);
            return cache.Get(cacheKey, () =>
            {
                var tables = this.GetTables(dbName);
                TableModel tm = tables.FirstOrDefault(p => p.TableName == tableName);
                if (tm.Columns == null || tm.Columns.Count == 0)
                {
                    tm.Columns = GetColumnInfoList(dbName, tableName);
                }
                return tm;
            });
        }

        public List<TableColumn> GetColumnInfoList(string dbName, string tableName)
        {
            if (string.IsNullOrWhiteSpace(dbName) || string.IsNullOrWhiteSpace(tableName)) return null;
            string cacheKey = string.Format(CacheKey_TABLE_COL, dbName, tableName);
            return cache.Get(cacheKey, () =>
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("select COL.TABLE_NAME 表名,");
                stringBuilder.Append("COL.COLUMN_ID as 序号,");
                stringBuilder.Append("COL.COLUMN_NAME as 字段名称,");
                stringBuilder.Append("COL.DATA_TYPE as 数据类型, ");
                stringBuilder.Append("DECODE(COL.DATA_TYPE, 'NUMBER',COL.DATA_PRECISION, COL.DATA_LENGTH) as 长度,");
                stringBuilder.Append("COL.DATA_PRECISION as 精度,");
                stringBuilder.Append("COL.DATA_SCALE as 范围,");
                stringBuilder.Append("'' as 标识,");
                stringBuilder.Append("case when PKCOL.COLUMN_POSITION >0  then '√'else '' end as 主键,");
                stringBuilder.Append("case when COL.NULLABLE='Y'  then '√'else '' end as 允许空, ");
                stringBuilder.Append("COL.DATA_DEFAULT as 默认值, ");
                stringBuilder.Append("CCOM.COMMENTS as 字段说明");

                stringBuilder.Append(" FROM USER_TAB_COLUMNS COL,USER_COL_COMMENTS CCOM, ");
                stringBuilder.Append(" ( SELECT AA.TABLE_NAME, AA.INDEX_NAME, AA.COLUMN_NAME, AA.COLUMN_POSITION");
                stringBuilder.Append(" FROM USER_IND_COLUMNS AA, USER_CONSTRAINTS BB");
                stringBuilder.Append(" WHERE BB.CONSTRAINT_TYPE = 'P'");
                stringBuilder.Append(" AND AA.TABLE_NAME = BB.TABLE_NAME");
                stringBuilder.Append(" AND AA.INDEX_NAME = BB.CONSTRAINT_NAME");
                if (!string.IsNullOrWhiteSpace(tableName))
                {
                    stringBuilder.Append(" AND AA.TABLE_NAME IN ('" + tableName + "')");
                }
                stringBuilder.Append(") PKCOL");
                stringBuilder.Append(" WHERE COL.TABLE_NAME = CCOM.TABLE_NAME");
                stringBuilder.Append(" AND COL.COLUMN_NAME = CCOM.COLUMN_NAME");
                if (!string.IsNullOrWhiteSpace(tableName))
                {
                    stringBuilder.Append(" AND COL.TABLE_NAME ='" + tableName + "'");
                }
                stringBuilder.Append(" AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+)");
                stringBuilder.Append(" AND COL.TABLE_NAME = PKCOL.TABLE_NAME(+)");
                stringBuilder.Append(" ORDER BY COL.TABLE_NAME, COL.COLUMN_ID ");

                var list = new List<TableColumn>();
                var dt = DbHelper.ExecuteDataTable(stringBuilder.ToString(), null);
                foreach (DataRow row in dt.Rows)
                {
                    var tc = new TableColumn();
                    tc.ColumnOrder = Utils.GetObjTranNull<int>(row["序号"].ToString());
                    tc.TableName = row["表名"].ToString();
                    tc.ColumnName = row["字段名称"].ToString();
                    tc.ColumnRemark = row["字段说明"].ToString();
                    tc.ColumnType = row["数据类型"].ToString();
                    tc.DefaultValue = row["默认值"].ToString();
                    tc.MaxLength = Utils.GetObjTranNull<int>(row["长度"].ToString());
                    tc.IsPrimaryKey = row["主键"].ToString() == "√" ? true : false;
                    tc.IsIdentity = row["标识"].ToString() == "√" ? true : false;
                    tc.IsNullable = row["允许空"].ToString() == "√" ? true : false;
                    list.Add(tc);
                }
                return list;
            });
        }

        public List<TableRelation> GetTableRelation(string tableName)
        {
            return new List<TableRelation>();
        }

        public void AddTableByKey(string dbName)
        {
            var list = this.GetTables(dbName);
            foreach (var item in list)
            {
                try
                {
                    DbHelper.
                        ExecuteNonQuery("ALTER TABLE " + item.TableName + " ADD CONSTRAINT TB_TEST_P_ID PRIMARY KEY(ID) ", null);
                    string cacheKey = CacheKey_TABLE_COL + item.TableName;
                    cache.Remove(cacheKey);
                }
                catch (Exception)
                {
                    continue;
                }
            }
        }
        /// <summary>
        /// 添加表（字段）描述
        /// </summary>
        /// <param name="addbyupdate"></param>
        /// <param name="describe"></param>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        public void ExtendedProperty(bool addbyupdate, string describe, string tableName, string columnName)
        {
            string sql = "";
            if (addbyupdate)
            {
                if (columnName == null || columnName.Length == 0)
                {
                    sql = "";
                }
                else
                {
                    sql = @"comment on column " + tableName.ToUpper() + "." + columnName.ToUpper() + " is ''''" + describe + "'''';";
                }
            }
            else
            {
                if (columnName == null || columnName.Length == 0)
                {
                    sql = "";
                }
                else
                {
                    sql = @"comment on column " + tableName.ToUpper() + "." + columnName.ToUpper() + " is ''''" + describe + "'''';";
                }
            }
            DbHelper.ExecuteNonQuery(sql, null);
            string cacheKey = CacheKey_TABLE_COL + tableName;
            cache.Remove(cacheKey);
        }
       
    }
}
